package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.Article;
import com.LostAndFound.utils.DataSourceUtils;

public class SearchForLoserDao {

	public int getTotalRecordForEasyUI(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 0 and foundflag = 1 and invalidflag = 1";
		Long total = 0L;
		if(cid.equals("all")) {
			total = (Long) runner.query(sql, new ScalarHandler());
		}else {
			sql = sql + " and cid = ?";
			total = (Long) runner.query(sql, new ScalarHandler(), cid);
		}
		return total.intValue();
	}
	public int getCheckTotalRecordForEasyUI(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 0 and foundflag = 2 and invalidflag = 2";
		Long total = 0L;
		if(cid.equals("all")) {
			total = (Long) runner.query(sql, new ScalarHandler());
		}else {
			sql = sql + " and cid = ?";
			total = (Long) runner.query(sql, new ScalarHandler(), cid);
		}
		return total.intValue();
	}
	public List<Map<String, Object>> getRecordListForSearchLoserToEasyUI(int startIndex, int pageSize, String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select a.*,u.*,c.cname from article a,user u,category c "
				+ " where whatflag = 0 and foundflag = 1 and invalidflag = 1 "
				+ " and a.uid = u.uid and a.cid = c.cid ";
		List<Map<String, Object>> mapList = null;
		
		if(cid.equals("all")) {
			sql = sql + " order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),startIndex,pageSize);
		}else {
			sql = sql + " and a.cid = ? order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),cid,startIndex,pageSize);
		}
		return mapList;
	}
	// 得到要进行审核的记录
	public List<Map<String, Object>> getCheckRecordListForSearchLoserToEasyUI(int startIndex, int pageSize, String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select a.*,u.*,c.cname from article a,user u,category c "
				+ " where whatflag = 0 and foundflag = 2 and invalidflag = 2 "
				+ " and a.uid = u.uid and a.cid = c.cid ";
		List<Map<String, Object>> mapList = null;
		
		if(cid.equals("all")) {
			sql = sql + " order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),startIndex,pageSize);
		}else {
			sql = sql + " and a.cid = ? order by a.issuetime desc limit ?,?";
			mapList = runner.query(sql, new MapListHandler(),cid,startIndex,pageSize);
		}
		return mapList;
	}
	public void handleDeleteSearchLoserRecordForEasyUI(String aid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where aid = ?";
		runner.update(sql, aid);
	}
	/*
	 * 隐藏此功能，2019/03/03
	 */

	public void handleAddNewSearchLoser(Article article) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into article values(?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {article.getAid(),article.getTitle(),article.getDetailInfo(),
				article.getIssueTime(),article.getImage(),article.getPhone(),
				article.getWhatFlag(),article.getFoundFlag(),article.getInvalidFlag(),
				article.getCategory().getCid(),article.getUser().getUid()};
		runner.update(sql, params);
	}

}
